﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace template_dll
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }
        public SqlConnection sqlconn = new SqlConnection();
        public int pharmcode;
        private DataTable cit = new DataTable();
        private DataTable regcit = new DataTable();
        private DataTable bra = new DataTable();
        private DataTable reg = new DataTable();
        private DataTable dir = new DataTable();
        private DataTable bma = new DataTable();
        private DataTable traf = new DataTable();
        private string city = "";

        private void Form2_Load(object sender, EventArgs e)
        {
            SqlCommand getpharm = sqlconn.CreateCommand();
            getpharm.CommandType = CommandType.StoredProcedure;
            getpharm.CommandText = "pas_PharmViewSel";
            getpharm.CommandTimeout = 30;

            getpharm.Parameters.Add("@pharmcode", SqlDbType.Int);
            getpharm.Parameters["@pharmcode"].Value = pharmcode;

            DataTable ph = new DataTable();
            if(sqlconn.State == ConnectionState.Closed) sqlconn.Open();
            ph.Load(getpharm.ExecuteReader());

            if (ph.Rows.Count == 1)
            {
                DataRow row = ph.Rows[0];
                string basemap = Convert.ToString(row["База МАП"]);
                string urs = Convert.ToString(row["УРС"]);
                string regurs = Convert.ToString(row["Регион"]);
                string region = Convert.ToString(row["Область"]);
                city = Convert.ToString(row["Город"]);
                string direct = Convert.ToString(row["Ответственный"]);
                string brand = Convert.ToString(row["Бренд"]);
                string dopen = Convert.ToString(row["Дата открытия"]);
                string dclos = Convert.ToString(row["Дата закрытия"]);
                string dreb = Convert.ToString(row["Дата ребрендинга"]);
                string traff = Convert.ToString(row["Трафик"]);
                bool open;
                if(Convert.ToString(row["Открыта"]) == "да") open = true;
                else open = false;
                float cred;
                if (Convert.IsDBNull(row["Кредит-нота"])) cred = 0;
                else cred = Convert.ToSingle(row["Кредит-нота"]) * 100;

                textBox1.Text = cred.ToString();

                bma.Load(SPSel("pas_BaseMASel", sqlconn));
                dir.Load(SPSel("pas_DirectorsSel", sqlconn));
                reg.Load(SPSel("pas_RegManagerSel", sqlconn));
                cit.Load(SPSel("pas_AllCitiesSel", sqlconn));
                bra.Load(SPSel("pas_BrandPharmSel", sqlconn));
                regcit.Load(SPSel("pas_RegionSel", sqlconn));
                traf.Load(SPSel("pas_TrafficSel", sqlconn));

                if (dopen != "")
                {
                    dateTimePicker1.Value = Convert.ToDateTime(dopen);
                    checkBox1.Checked = true;
                    dateTimePicker1.Enabled = true;
                }
                else
                {
                    checkBox1.Checked = false;
                    dateTimePicker1.Enabled = false;
                }

                if (dclos != "")
                {
                    dateTimePicker2.Value = Convert.ToDateTime(dclos);
                    checkBox2.Checked = true;
                    dateTimePicker2.Enabled = true;
                }
                else
                {
                    checkBox2.Checked = false;
                    dateTimePicker2.Enabled = false;
                }

                if (dreb != "")
                {
                    dateTimePicker3.Value = Convert.ToDateTime(dreb);
                    checkBox3.Checked = true;
                    dateTimePicker3.Enabled = true;
                }
                else
                {
                    checkBox3.Checked = false;
                    dateTimePicker3.Enabled = false;
                }


                comboBox1 = AddItems(comboBox1, bma, "BaseName", basemap);
                comboBox4 = AddItems(comboBox4, dir, "ShortName", direct);
                comboBox2 = AddItems(comboBox2, regcit, "RegionName", region);
                comboBox3 = AddItemsCity(comboBox3, cit, "CityName",comboBox2.Text,city);
                comboBox8 = AddItems(comboBox8, traf, "TrafficName", traff);

                comboBox7 = AddItems(comboBox7, bra, "BrandPharmName", brand);
                foreach (DataRow dr in reg.Rows)
                {
                    comboBox5.Items.Add(Convert.ToString(dr["Manager"]));
                    comboBox6.Items.Add(Convert.ToString(dr["RegionName"]));
                    if (Convert.ToString(dr["Manager"]) == urs)
                        comboBox5.SelectedIndex = comboBox5.Items.Count - 1;
                    if (Convert.ToString(dr["RegionName"]) == regurs)
                        comboBox6.SelectedIndex = comboBox6.Items.Count - 1;
                }
                checkBox4.Checked = open;

                label1.Text = Convert.ToString(row["Код"]) + "  " + Convert.ToString(row["Аптека сокр"]);
            }
            if (sqlconn.State == ConnectionState.Open) sqlconn.Close();
        }

        private ComboBox AddItems(ComboBox cb, DataTable dt, string columnname, string pos)
        {
            foreach (DataRow dr in dt.Rows)
            {
                cb.Items.Add(Convert.ToString(dr[columnname]));
                if (Convert.ToString(dr[columnname]) == pos) cb.SelectedIndex = cb.Items.Count - 1;
            }
            return cb;
        }

        private ComboBox AddItemsCity(ComboBox cb, DataTable dt, string columnname, string reg,string pos)
        {
            cb.Items.Clear();
            foreach (DataRow dr in dt.Rows)
            {
                if (Convert.ToString(dr["RegionName"]) == reg)
                {
                    cb.Items.Add(Convert.ToString(dr[columnname]));
                    if (Convert.ToString(dr[columnname]) == pos) cb.SelectedIndex = cb.Items.Count - 1;
                }
            }
            return cb;
        }

        private SqlDataReader SPSel(string spname, SqlConnection con)
        {
            SqlCommand mycomm = con.CreateCommand();
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.CommandText = spname;
            mycomm.CommandTimeout = 30;
            if(con.State == ConnectionState.Closed) con.Open();
            return mycomm.ExecuteReader();
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox1.Checked) dateTimePicker1.Enabled = true;
            else dateTimePicker1.Enabled = false;
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox2.Checked) dateTimePicker2.Enabled = true;
            else dateTimePicker2.Enabled = false;
        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox3.Checked) dateTimePicker3.Enabled = true;
            else dateTimePicker3.Enabled = false;
        }

        private void comboBox5_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox6.SelectedIndex = comboBox5.SelectedIndex;
        }

        private void comboBox6_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox5.SelectedIndex = comboBox6.SelectedIndex;
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox3.Items.Clear();
            comboBox3.Text = "";
            comboBox3 = AddItemsCity(comboBox3, cit, "CityName", comboBox2.SelectedItem.ToString(),city);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlCommand spins = sqlconn.CreateCommand();
            spins.CommandType = CommandType.StoredProcedure;
            spins.CommandText = "pas_PharmAttribIns";
            spins.CommandTimeout = 30;

            spins = AddParam(spins, DbType.Int32, "@pharmcode", pharmcode);
            spins = AddParam(spins, DbType.Int32, "@base", SearchID(bma, "BaseName",comboBox1.Text,"BaseID"));
            spins = AddParam(spins, DbType.Int32, "@urs", SearchID(reg,"RegionName",comboBox6.Text,"RegionManagerID"));
            spins = AddParam(spins, DbType.Int32, "@city", SearchID(cit,"CityName",comboBox3.Text,"CityID"));
            spins = AddParam(spins, DbType.Int32, "@reg", SearchID(regcit,"RegionName",comboBox2.Text,"RegionID"));
            spins = AddParam(spins, DbType.Int32, "@dir", SearchID(dir,"ShortName",comboBox4.Text,"DirectorID"));
            spins = AddParam(spins, DbType.Int32, "@brand", SearchID(bra,"BrandPharmName",comboBox7.Text,"BrandPharmID"));
            spins = AddParam(spins, DbType.Date, "@dateop", DateTimeCheck(dateTimePicker1,checkBox1));
            spins = AddParam(spins, DbType.Date, "@datecl", DateTimeCheck(dateTimePicker2,checkBox2));
            spins = AddParam(spins, DbType.Date, "@datereb", DateTimeCheck(dateTimePicker3,checkBox3));
            spins = AddParam(spins, DbType.Boolean, "@open", checkBox4.Checked);
            spins = AddParam(spins, DbType.Int32, "@traf", SearchID(traf, "TrafficName", comboBox8.Text, "TrafficID"));
            //if(textBox1.Text != "") spins = AddParam(spins, DbType.Single, "@crednote", Convert.ToSingle(textBox1.Text) / 100);
            if (comboBox1.Text != "")
            {
                if (comboBox1.Text == "сибирская") AddParam(spins, DbType.Single, "@crednote", 0);
                else AddParam(spins, DbType.Single, "@crednote", 0.0277);
            }
            if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
            spins.ExecuteNonQuery();
        }

        private object DateTimeCheck(DateTimePicker dtp,CheckBox cb)
        {
            if (cb.Checked) return dtp.Value;
            else return DBNull.Value;
        }

        private SqlCommand AddParam(SqlCommand c,DbType dt,string param,object val)
        {
            c.Parameters.AddWithValue(param, val);
            //c.Parameters[param].Value = val;
            return c;
        }

        private object SearchID(DataTable dt, string columnname, string val,string col_id)
        {
            foreach (DataRow dr in dt.Rows)
                if (Convert.ToString(dr[columnname]) == val) return Convert.ToInt32(dr[col_id]);
            
            return DBNull.Value;
        }

        private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == '.') e.KeyChar = ',';
            if (!(Char.IsDigit(e.KeyChar)) && !((e.KeyChar == ',') && (textBox1.Text.IndexOf(",") == -1) && (textBox1.Text.Length != 0)))
            {
                if (e.KeyChar != (char)Keys.Back)
                {
                    e.Handled = true;
                }
            }
        }
    }
}
